﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using PPAP.Model;
using System.Data.OracleClient;

namespace PPAP.DAO
{
    public class AttachmentTemplateDAO
    {
        public AttachmentTemplateDAO() { }
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(AttachmentTemplate model)
        {
            model.ID = DbHelperOra.GetMaxID("ID", "ATTACHMENTTEMPLATE");
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into ATTACHMENTTEMPLATE(");
            strSql.Append("ID,TEMPLATE,ATTACHMENTTYPEID,REMARK,BDNTTEMPLATEAVAILABLE,SUPPLIERSTEMPLATEACCEPTED,ISLEVEL1,ISLEVEL2,ISLEVEL3,UPLOADEDFILE,UPLOADEDBY,LASTMODIFIEDDATE)");
            strSql.Append(" values (");
            strSql.Append(":ID,:TEMPLATE,:ATTACHMENTTYPEID,:REMARK,:BDNTTEMPLATEAVAILABLE,:SUPPLIERSTEMPLATEACCEPTED,:ISLEVEL1,:ISLEVEL2,:ISLEVEL3,:UPLOADEDFILE,:UPLOADEDBY,:LASTMODIFIEDDATE)");
            OracleParameter[] parameters = {
					new OracleParameter(":ID", OracleType.Number,4),
					new OracleParameter(":TEMPLATE", OracleType.VarChar,50),
					new OracleParameter(":ATTACHMENTTYPEID", OracleType.Number,4),
					new OracleParameter(":REMARK", OracleType.VarChar,50),
					new OracleParameter(":BDNTTEMPLATEAVAILABLE", OracleType.Number,4),
					new OracleParameter(":SUPPLIERSTEMPLATEACCEPTED", OracleType.Number,4),
					new OracleParameter(":ISLEVEL1", OracleType.Number,4),
					new OracleParameter(":ISLEVEL2", OracleType.Number,4),
					new OracleParameter(":ISLEVEL3", OracleType.Number,4),
					new OracleParameter(":UPLOADEDFILE", OracleType.VarChar,50),
					new OracleParameter(":UPLOADEDBY", OracleType.VarChar,50),
					new OracleParameter(":LASTMODIFIEDDATE", OracleType.DateTime)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.TEMPLATE;
            parameters[2].Value = model.ATTACHMENTTYPEID;
            parameters[3].Value = model.REMARK;
            parameters[4].Value = model.BDNTTEMPLATEAVAILABLE;
            parameters[5].Value = model.SUPPLIERSTEMPLATEACCEPTED;
            parameters[6].Value = model.ISLEVEL1;
            parameters[7].Value = model.ISLEVEL2;
            parameters[8].Value = model.ISLEVEL3;
            parameters[9].Value = model.UPLOADEDFILE;
            parameters[10].Value = model.UPLOADEDBY;
            parameters[11].Value = model.LASTMODIFIEDDATE;

           return DbHelperOra.ExecuteSql(strSql.ToString(), parameters)>0?int.Parse(model.ID.ToString()):0;
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public int Update(AttachmentTemplate model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ATTACHMENTTEMPLATE set ");
            strSql.Append("ID=:ID,");
            strSql.Append("TEMPLATE=:TEMPLATE,");
            strSql.Append("ATTACHMENTTYPEID=:ATTACHMENTTYPEID,");
            strSql.Append("REMARK=:REMARK,");
            strSql.Append("BDNTTEMPLATEAVAILABLE=:BDNTTEMPLATEAVAILABLE,");
            strSql.Append("SUPPLIERSTEMPLATEACCEPTED=:SUPPLIERSTEMPLATEACCEPTED,");
            strSql.Append("ISLEVEL1=:ISLEVEL1,");
            strSql.Append("ISLEVEL2=:ISLEVEL2,");
            strSql.Append("ISLEVEL3=:ISLEVEL3,");
            strSql.Append("UPLOADEDFILE=:UPLOADEDFILE,");
            strSql.Append("UPLOADEDBY=:UPLOADEDBY,");
            strSql.Append("LASTMODIFIEDDATE=:LASTMODIFIEDDATE");
            strSql.Append(" where id="+model.ID);
            OracleParameter[] parameters = {
					new OracleParameter(":ID", OracleType.Number,4),
					new OracleParameter(":TEMPLATE", OracleType.VarChar,50),
					new OracleParameter(":ATTACHMENTTYPEID", OracleType.Number,4),
					new OracleParameter(":REMARK", OracleType.VarChar,50),
					new OracleParameter(":BDNTTEMPLATEAVAILABLE", OracleType.Number,4),
					new OracleParameter(":SUPPLIERSTEMPLATEACCEPTED", OracleType.Number,4),
					new OracleParameter(":ISLEVEL1", OracleType.Number,4),
					new OracleParameter(":ISLEVEL2", OracleType.Number,4),
					new OracleParameter(":ISLEVEL3", OracleType.Number,4),
					new OracleParameter(":UPLOADEDFILE", OracleType.VarChar,50),
					new OracleParameter(":UPLOADEDBY", OracleType.VarChar,50),
					new OracleParameter(":LASTMODIFIEDDATE", OracleType.DateTime)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.TEMPLATE;
            parameters[2].Value = model.ATTACHMENTTYPEID;
            parameters[3].Value = model.REMARK;
            parameters[4].Value = model.BDNTTEMPLATEAVAILABLE;
            parameters[5].Value = model.SUPPLIERSTEMPLATEACCEPTED;
            parameters[6].Value = model.ISLEVEL1;
            parameters[7].Value = model.ISLEVEL2;
            parameters[8].Value = model.ISLEVEL3;
            parameters[9].Value = model.UPLOADEDFILE;
            parameters[10].Value = model.UPLOADEDBY;
            parameters[11].Value = model.LASTMODIFIEDDATE;

           return DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public int Delete(int id)
        {
            //该表无主键信息，请自定义主键/条件字段
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from ATTACHMENTTEMPLATE ");
            strSql.Append(" where id="+id);
            OracleParameter[] parameters = {
};

           return DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public AttachmentTemplate GetModel(int id)
        {
            //该表无主键信息，请自定义主键/条件字段
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select p.attachmenttype,t.* from ATTACHMENTTYPE p left join ATTACHMENTTEMPLATE t on p.id=t.attachmenttypeid ");
            strSql.Append(" where t.id="+id);
            OracleParameter[] parameters = {
};

            AttachmentTemplate model = new AttachmentTemplate();
            DataSet ds = DbHelperOra.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                model.TEMPLATE = ds.Tables[0].Rows[0]["TEMPLATE"].ToString();
                if (ds.Tables[0].Rows[0]["ATTACHMENTTYPEID"].ToString() != "")
                {
                    model.ATTACHMENTTYPEID = int.Parse(ds.Tables[0].Rows[0]["ATTACHMENTTYPEID"].ToString());
                }
                model.REMARK = ds.Tables[0].Rows[0]["REMARK"].ToString();
                if (ds.Tables[0].Rows[0]["BDNTTEMPLATEAVAILABLE"].ToString() != "")
                {
                    model.BDNTTEMPLATEAVAILABLE = int.Parse(ds.Tables[0].Rows[0]["BDNTTEMPLATEAVAILABLE"].ToString());
                }
                if (ds.Tables[0].Rows[0]["SUPPLIERSTEMPLATEACCEPTED"].ToString() != "")
                {
                    model.SUPPLIERSTEMPLATEACCEPTED = int.Parse(ds.Tables[0].Rows[0]["SUPPLIERSTEMPLATEACCEPTED"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ISLEVEL1"].ToString() != "")
                {
                    model.ISLEVEL1 = int.Parse(ds.Tables[0].Rows[0]["ISLEVEL1"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ISLEVEL2"].ToString() != "")
                {
                    model.ISLEVEL2 = int.Parse(ds.Tables[0].Rows[0]["ISLEVEL2"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ISLEVEL3"].ToString() != "")
                {
                    model.ISLEVEL3 = int.Parse(ds.Tables[0].Rows[0]["ISLEVEL3"].ToString());
                }
                model.UPLOADEDFILE = ds.Tables[0].Rows[0]["UPLOADEDFILE"].ToString();
                model.UPLOADEDBY = ds.Tables[0].Rows[0]["UPLOADEDBY"].ToString();
                if (ds.Tables[0].Rows[0]["LASTMODIFIEDDATE"].ToString() != "")
                {
                    model.LASTMODIFIEDDATE = DateTime.Parse(ds.Tables[0].Rows[0]["LASTMODIFIEDDATE"].ToString());
                }
                model.Attachmenttype = ds.Tables[0].Rows[0]["Attachmenttype"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,TEMPLATE,ATTACHMENTTYPEID,REMARK,BDNTTEMPLATEAVAILABLE,SUPPLIERSTEMPLATEACCEPTED,ISLEVEL1,ISLEVEL2,ISLEVEL3,UPLOADEDFILE,UPLOADEDBY,LASTMODIFIEDDATE ");
            strSql.Append(" FROM ATTACHMENTTEMPLATE ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperOra.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取数据列表
        /// </summary>
        /// <returns></returns>
        public DataSet GetList()
        {
            string sql = "select a.*,rownum from (select p.attachmenttype,t.* from ATTACHMENTTYPE p left join ATTACHMENTTEMPLATE t on p.id=t.attachmenttypeid where p.xs=1  order by p.sx)a ";
            return DbHelperOra.Query(sql);
        }
    }
}
